package com.metagurukul.metagranth.model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.metagurukul.metagranth.exception.MetaSqlException;
import com.metagurukul.metagranth.model.common.connection.ConnectionPoolFactory;
import com.metagurukul.metagranth.model.common.dbutil.Util;
import com.metagurukul.metagranth.vo.Question;
import com.metagurukul.metagranth.vo.Tag;
/**
 * 
 * @author team metaGranth
 * This class will handle all the operations related to question table of the database.
 * This class will send or work on the question
 *
 */
public class QuestionDAO {

	/**
	 * 
	 * @param rs stores the result set object retrieved after executing the SQL query
	 * @return this function will return an arrayList of the Question value object....which store all the column value of the Question table
	 */

	private static ArrayList<Question> processResultSet(ResultSet rs) {
		ArrayList<Question> questionsList = new ArrayList<Question>();
		Question question = null;
		try {
			while (rs.next()) {
				question = new Question();
				//qid will store the column value of question_identification_number of table question
				int qid = rs.getInt(1);
				question.setQuesId(qid);
				//title will store the column value of title of table question
				String title = rs.getString(2);
				question.setTitle(title);
				//ques will store the column value of question of table question
				String ques = rs.getString(3);
				question.setQuestion(ques);
				//uid will store the column value of user_identification_number of table question
				int uid = rs.getInt(4);
				question.setUserId(uid);
				//dop will store column value of Date_of_post of table question
				Timestamp dop = rs.getTimestamp(5);
				question.setDateOfPost(dop);
				//closedate will store column value of close_date of table question
				Date closedate = rs.getDate(6);
				question.setCloseDate(closedate);
				//isclose will store column value of isClosed of table question
				boolean isclose = rs.getBoolean(7);
				question.setIsClosed(isclose);
				//the complete question object is added to the questionList
				questionsList.add(question);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return questionsList;
	}
	/**
	 * 
	 * @param number store number of latest question to be retrieved
	 * @return	list of question value object
	 * @throws MetaSqlException 
	 */
	public static ArrayList<Question> searchLatestQuestion(int number)throws MetaSqlException {
		ArrayList<Question> questionsList = null;
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//query stores the SQL query to be executed
		String query = "SELECT * FROM metacubegranth_question ORDER BY DATE_OF_POST DESC LIMIT ?";
		//ps statement is the required prepared statement used for executing the query
		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, number);
			//rs stores the result set
			ResultSet rs = ps.executeQuery();
			//questionList store the list of question value object returned from the processResultSet function
			questionsList = processResultSet(rs);

		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);


		} finally {
			//close all the open database connection
			Util.closePreparedStatement(ps);
			//checkIn the connection again to the ConnectionPoolFactory
			cp.checkIn(con);
		}

		return questionsList;
	}
	/**
	 * 
	 * @param question stores the question value object
	 * @return will return the status whether the question has been saved or not in the database
	 * @throws MetaSqlException
	 */
	public static int save(Question question) throws MetaSqlException {
		//connectionpoolFactory store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory connectionPoolFactory = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = connectionPoolFactory.checkOut();
		//questionTitle stores the title of the question stored in the question value object
		String questionTitle = question.getTitle();
		//questionDescription stores the question stored in the question value object
		String questionDescription = question.getQuestion();
		//uid stores the user_identification_number stored in the question value object
		int uid = question.getUserId();
		//query stores the SQL query whic is to be executed
		String query = "INSERT INTO metacubegranth_question (TITLE,QUESTION,USER_IDENTIFICATION_NUMBER) VALUES(?,?,?)";
		//ps stores the prepared statement
		PreparedStatement ps = null;
		int generatedQuestionId = 0;
		try {
			
			ps = con.prepareStatement(query, ps.RETURN_GENERATED_KEYS);
			/* set variable in prepared statement */
			ps.setString(1, questionTitle);
			ps.setString(2, questionDescription);
			ps.setInt(3, uid);
			//execute the query
			ps.executeUpdate();
			//rs stores the ResultSet object returned after the query is executed
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				generatedQuestionId = rs.getInt(1);
			}
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close the prepared statement
			Util.closePreparedStatement(ps);
			//close the connection and return the connection back to the ConnectionPoolFactory 
			connectionPoolFactory.checkIn(con);
		}
		return generatedQuestionId;
	}
	
	/**
	 * 
	 * @param question stores the question value object
	 * @return will return the list of the question value object
	 * @throws MetaSqlException
	 */
	public static ArrayList<Question> searchByQuestion(Question question)
	throws MetaSqlException {
		//searchSet will store the list of the question value object
		ArrayList<Question> searchset = new ArrayList<Question>();
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//questionTitle store the title of the question which is to be searched
		String questionTitle = question.getTitle();
		//query stores the SQL query which is to executed
		String query = "select * from metacubegranth_question where Question LIKE ?";
		//ps stores the prepared statement for executing the query
		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, "%" + questionTitle + "%");
			//rs stores the Result Set returned by executing the query
			ResultSet rs = ps.executeQuery();
			searchset = processResultSet(rs);
			
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
						throw new MetaSqlException(e);
		} finally {
			//close the prepared statement
			Util.closePreparedStatement(ps);
			//close the connection and return the connection back to the ConnectionPoolFactory 
			cp.checkIn(con);
		}

		return searchset;
	}
	/**
	 * 
	 * @param question stores the question value object whic stores the question data that is to be closed
	 * @return	status whether the question has been closed or not
	 * @throws MetaSqlException
	 */
	public static int closeQuestion(Question question) throws MetaSqlException {
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//qid stores the question_identification_number of the question whic is to be closed
		int qid = question.getQuesId();
		//query stores the SQL query to be executed
		String query = "UPDATE metacubegranth_question SET IS_CLOSED = TRUE WHERE QUESTION_IDENTIFICATION_NUMBER = ?";
		//ps stores the prepared statement which executes the query
		PreparedStatement ps = null;
		int status = -1;
		try {

			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, qid);
			//status stores whether update is successful or not
			status = ps.executeUpdate();
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw  new MetaSqlException(e);
		} finally {
			//close the prepared statement
			Util.closePreparedStatement(ps);
			//close the connection and return the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
		return status;

	}
	
	/**
	 * 
	 * @param questionId stores the question_identification_number of the question which is to be retrieved
	 * @return	an object of the question value object
	 * @throws MetaSqlException
	 */
	public static Question searchQuestionbyId(int questionId) throws MetaSqlException {
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//query will store the SQL query to be executed
		String query = "select * from metacubegranth_question where QUESTION_IDENTIFICATION_NUMBER = ?";
		//ps stores the prepared statement whic helps in executin the query
		PreparedStatement ps = null;
		//resultantQuestionSet object will store the question value object 
		Question resultantQuestionSet =new Question();

		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, questionId);
			//rs stores the ResultSet of the query whic is executed
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				//qid will store the column value of question_identification_number of table question
				int qid = rs.getInt(1);
				resultantQuestionSet.setQuesId(qid);
				//title will store the column value of title of table question
				String title = rs.getString(2);
				resultantQuestionSet.setTitle(title);
				//ques will store the column value of question of table question
				String ques = rs.getString(3);
				resultantQuestionSet.setQuestion(ques);
				//uid will store the column value of user_identification_number of table question
				int uid = rs.getInt(4);
				resultantQuestionSet.setUserId(uid);
				//dop will store column value of Date_of_post of table question
				Timestamp dop = rs.getTimestamp(5);
				resultantQuestionSet.setDateOfPost(dop);
				//closedate will store column value of close_date of table question
				Date closedate = rs.getDate(6);
				resultantQuestionSet.setCloseDate(closedate);
				//isclose will store column value of isClosed of table question
				boolean isclose = rs.getBoolean(7);
				resultantQuestionSet.setIsClosed(isclose);
				
			}

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the prepared statement
			Util.closePreparedStatement(ps);
			//close the database connection and checkIn the Connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}

		return resultantQuestionSet;
	}
	/**
	 * 
	 * @return the list of the question value objects
	 * This function will return all the question present in the database
	 * @throws MetaSqlException
	 */
	public static ArrayList<Question> getAllQuestions() throws MetaSqlException {
		//questionList stores the question value objects 
		ArrayList<Question> questionsList = null;

		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//query stores the SQL query to be executed
		String query = "SELECT * FROM metacubegranth_question ORDER BY DATE_OF_POST";
		//ps stores the Prepared Statement
		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			//rs stores the Result Set after executing the query
			ResultSet rs = ps.executeQuery();
		
			questionsList = processResultSet(rs);

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);

		} finally {
			//close all the prepared statement
			Util.closePreparedStatement(ps);
			//close the database connection and checkIn the Connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}

		return questionsList;
	}
	/**
	 * 
	 * @param question stores the question string
	 * @return list of all the question searched on the basis of a question provided by the user
	 * @throws MetaSqlException
	 */
	public static ArrayList<Question> searchByQuestion(String question)throws MetaSqlException {
		//searchSet stores the list of all the question value objects
		ArrayList<Question> searchset = new ArrayList<Question>();
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//query stores the SQL query to be executed
		String query = "select * from metacubegranth_question where Question LIKE ? OR TITLE LIKE ?";
		//ps stores the prepared statement
		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, "%" + question + "%");
			ps.setString(2, "%" + question + "%");
			//rs store the Result Set which is returned after the query has been executed
			ResultSet rs = ps.executeQuery();
			
			searchset = processResultSet(rs);

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the prepared statement
			Util.closePreparedStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}

		return searchset;
	}
	/**
	 * 
	 * @param tagObject stores the tag value object 
	 * @return a list of the question value objects 
	 * searching is performed on basis of tag_identification_number which is retrieved from the tag value object
	 * @throws MetaSqlException
	 */
	public ArrayList<Question> searchByTag(Tag tagObject)throws MetaSqlException {

		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//tagId stores the tag_identification_number of the tag for which we have to find the question
		int tagId = tagObject.getTagid();
		//questionList stores the question value objects
		ArrayList<Question> questionList = null;
		//query stores the SQL query which is to be executed
		String query = "Select * from METACUBEGRANTH_QUESTION where QUESTION_IDENTIFICATION_NUMBER IN("
			+ "Select QUESTION_IDENTIFICATION_NUMBER from METACUBEGRANTH_QUESTION_TAG where TAG_IDENTIFICATION_NUMBER =?)";
		//ps stores the prepared statement
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(query);
			ps.setInt(1, tagId);
			//rs stores the Result Set 
			ResultSet rs = ps.executeQuery();
			questionList = processResultSet(rs);
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the prepared statement
			Util.closePreparedStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
		return questionList;
	}

	/**
	 * this function will return the questions list for admin panel, (those
	 * questions which are not closed) and have atleast one answer.
	 */

	public static ArrayList<Question> getAllQuestionsForAdmin()throws MetaSqlException {
		//questionList will store the list of the question value objects
		ArrayList<Question> questionsList = null;
		//cp store the database connection returned by the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		//con stores the connection which has been checked out from function checkout from the ConnectionPoolFactory
		Connection con = cp.checkOut();
		//query stores the SQL query which is to be executed
		String query = "SELECT * FROM metacubegranth_question q WHERE q.is_closed=0 AND q.question_identification_number IN(select question_identification_number from metacubegranth_answer) ORDER BY DATE_OF_POST";
		//ps stores the Prepared Statement
		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			//rs stores the Result Set object returned after executing the query
			ResultSet rs = ps.executeQuery();
			questionsList = processResultSet(rs);

		} catch (SQLException e) {
			
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);

		} finally {
			//close all the prepared statement
			Util.closePreparedStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
			return questionsList;
	}
	/**
	 * 
	 * @param answerId stores the answer id of the answer whose corresponding question is to be updated
	 * @return	status whether the status has been updated or not
	 * @throws MetaSqlException
	 */
	public static int updateQuestionStatus(int answerId)throws MetaSqlException {
		//connectionpoolFactory will retrieve the connection from the ConnectionPoolFactory class
		ConnectionPoolFactory connectionPoolFactory = ConnectionPoolFactory.getInstance();
		//con stores the connection 
		Connection con = connectionPoolFactory.checkOut();
		//pstmt stores the prepared statement
		PreparedStatement pstmt = null;
		int noOfRowsUpdated = -1;
		
		String query = "UPDATE metacubegranth_question SET is_closed = 1 WHERE question_identification_number = (SELECT question_identification_number from metacubegranth_answer WHERE answer_identification_number = ?)";
		try {
			pstmt = con.prepareStatement(query);

			pstmt.setInt(1, answerId);
			noOfRowsUpdated = pstmt.executeUpdate();
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);

		} finally {
			//close all the prepared statement
			Util.closeStatement(pstmt);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			connectionPoolFactory.checkIn(con);
		}

		return noOfRowsUpdated;
	}
	
	/**
	 * 
	 * @return number of question whic are closed by the admin
	 * @throws MetaSqlException
	 */
	public static int countClosedQuestion() throws MetaSqlException {
		//connectionpoolFactory will retrieve the connection from the ConnectionPoolFactory class
		ConnectionPoolFactory connectionPoolFactory = ConnectionPoolFactory.getInstance();
		//con stores the connection 
		Connection con = connectionPoolFactory.checkOut();

		PreparedStatement pstmt = null;
		int count = 0;

		String query = "SELECT COUNT(*) FROM metacubegranth_question  WHERE is_closed=1";
		try {
			pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the prepared statement
			Util.closeStatement(pstmt);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			connectionPoolFactory.checkIn(con);
		}

		return count;
	}
	
	/**
	 * 
	 * @param userId stores the userId of the current user
	 * @return	list of the question value objects
	 * this function will return all the question which are unanswered of the particular user
	 * @throws MetaSqlException 
	 */
	public static ArrayList<Question> getEditableQuestions(int userId) throws MetaSqlException {
		ResultSet resultSet = null ;
		//editableQuestions is a list which store the question value objects 
		ArrayList<Question> editableQuestions = new ArrayList<Question>();

		// creating connection
		ConnectionPoolFactory connectionPoolFactory = ConnectionPoolFactory.getInstance();
		Connection con = connectionPoolFactory.checkOut();
		Statement stmt=null;
		try {
			// creating statement
		 stmt = con.createStatement();

			String query = " SELECT * FROM metacubegranth_question WHERE USER_IDENTIFICATION_NUMBER = "+userId+" AND QUESTION_IDENTIFICATION_NUMBER NOT IN (SELECT QUESTION_IDENTIFICATION_NUMBER FROM metacubegranth_answer)";

			resultSet = stmt.executeQuery(query);
			editableQuestions=processResultSet(resultSet);
		}catch (SQLException e) {			
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		}finally {
			//close all the  statement
			Util.closeStatement(stmt);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			connectionPoolFactory.checkIn(con);
		}				

		return editableQuestions ;		
	}
	/**
	 * 
	 * @return list of the question value objects
	 * this function will return all the question which are not yet answered
	 * @throws MetaSqlException
	 */

	public static ArrayList<Question> searchUnansweredQuestion() throws MetaSqlException {
		ArrayList<Question> questionsList = null;
		// create and retrieve connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		String query = "SELECT * FROM metacubegranth_question q WHERE q.is_closed=0 AND q.question_identification_number NOT IN(select question_identification_number from metacubegranth_answer) ORDER BY DATE_OF_POST";

		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(query);
			
			ResultSet rs = ps.executeQuery();
			questionsList = processResultSet(rs);

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);

		} finally {
			//close all the  statement
			Util.closeStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
		
		return questionsList;
	}
	/**
	 * 
	 * @param quesid stores the question_identification_number of the question which we have to search
	 * @return	question value object
	 * this function will find a question based on its identification_number
	 * @throws MetaSqlException 
	 */
	public static Question getQuestionbyId(int quesid) throws MetaSqlException {
		// create and retrieve connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();

		String query = "select * from metacubegranth_question where QUESTION_IDENTIFICATION_NUMBER = ?";

		PreparedStatement ps = null;
		//resultantQuestionSet will store the question value object which is to be returned from the function
		Question resultantQuestionSet = null;

		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, quesid);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				resultantQuestionSet = new Question();
				int questionId = rs.getInt(1);
				resultantQuestionSet.setQuesId(questionId);
				String title = rs.getString(2);
				resultantQuestionSet.setTitle(title);
				String ques = rs.getString(3);
				resultantQuestionSet.setQuestion(ques);
				int userId = rs.getInt(4);
				resultantQuestionSet.setUserId(userId);
				Date dateOfPost = rs.getDate(5);
				resultantQuestionSet.setDateOfPost(dateOfPost);
				Timestamp closeDate = rs.getTimestamp(6);
				resultantQuestionSet.setCloseDate(closeDate);
				boolean isClose = rs.getBoolean(7);
				resultantQuestionSet.setIsClosed(isClose);

			}

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the  statement
			Util.closeStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}

		return resultantQuestionSet;
	}
	
	/**
	 * 
	 * @param questionObj store the object of the question which is to be updated in the database
	 * @throws MetaSqlException 
	 */
	public static void updateEditedQuestion(Question questionObj) throws MetaSqlException{
		// create and retrieve connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		int questionID = questionObj.getQuesId();
		String updatedTitle = questionObj.getTitle();
		String updatedQuestion = questionObj.getQuestion();
		Statement stmt = null ;

		try {
			// creating statement
			stmt = con.createStatement();

			// executing query
			String query = "UPDATE metacubegranth_question SET TITLE='"+updatedTitle+"' , QUESTION='"+updatedQuestion+"' WHERE QUESTION_IDENTIFICATION_NUMBER="+questionID;
			stmt.executeUpdate(query);

		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		}finally {
			//close all the  statement
			Util.closeStatement(stmt);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
	}
	
	/**
	 * 
	 * @param tag will store the tag object whic is to be used during the search operation of the question
	 * @return	the list of the question value objects
	 * @throws MetaSqlException
	 */
	public static ArrayList<Question> searchByTagObject(Tag tag) throws MetaSqlException {
		// create and retrieve connection from the ConnectionPoolFactory
		
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		int tagId = tag.getTagid();
		ArrayList<Question> questionList = null;
		String query = "Select * from METACUBEGRANTH_QUESTION where QUESTION_IDENTIFICATION_NUMBER IN(Select QUESTION_IDENTIFICATION_NUMBER from METACUBEGRANTH_QUESTION_TAG where TAG_IDENTIFICATION_NUMBER = ?)";
		PreparedStatement ps = null;
		try {

			ps = con.prepareStatement(query);
			ps.setInt(1, tagId);
			ResultSet rs = ps.executeQuery();

			questionList = processResultSet(rs);
		} catch (SQLException e) {
			//if there is any SQL exception then throw that exception
			throw new MetaSqlException(e);
		} finally {
			//close all the  statement
			Util.closeStatement(ps);
			//close all the database connection ...and check in the connection to the ConnectionPoolFactory
			cp.checkIn(con);
		}
		return questionList;

	}
}
